﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Cosmos.Database;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.IO;
using System.Drawing.Imaging;

namespace CoastalClimbing.Code
{
    public static class DAL
    {
        private static void ExecuteNonQuery(string sproc, SqlParameter[] parameters)
        {
            using (SqlConnection cnt = new SqlConnection(ConfigurationManager.ConnectionStrings["CoastalClimbing"].ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sproc, cnt))
                {
                    cnt.Open();              
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddRange(parameters);
                    cmd.ExecuteNonQuery();                    
                }
            }
        }

        private static DataTable RetrieveDataTable(string sproc, SqlParameter[] parameters)
        {
            DataTable dt = new DataTable();

            using (SqlConnection cnt = new SqlConnection(ConfigurationManager.ConnectionStrings["CoastalClimbing"].ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sproc, cnt))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    if (parameters != null)
                        cmd.Parameters.AddRange(parameters);

                    using (SqlDataAdapter dta = new SqlDataAdapter(cmd))
                    {                    
                        cnt.Open();
                        dta.Fill(dt);
                    }
                }                
            }

            return dt;
        }

        private static object RetrieveScalar(string sproc, SqlParameter[] parameters)
        {
            object o;

            using (SqlConnection cnt = new SqlConnection(ConfigurationManager.ConnectionStrings["CoastalClimbing"].ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sproc, cnt))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    if (parameters != null)
                        cmd.Parameters.AddRange(parameters);

                    cnt.Open();
                    o = cmd.ExecuteScalar();
                }
            }

            return o;
        }
        
        public static void LogException(Exception ex)
        {
            var parameters = new[]{
                new SqlParameter(){ ParameterName="@MESSAGE", Value=ex.Message },
                new SqlParameter(){ ParameterName="@STACKTRACE", Value=ex.StackTrace },                
                new SqlParameter(){ ParameterName="@SOURCE", Value=ex.Source }
            };

            ExecuteNonQuery("LOG_CREATE", parameters);
        }

        public static void LinkUsers(string username, string userid)
        {
            var parameters = new[]{
                new SqlParameter(){ ParameterName="@USERNAME", Value = username },
                new SqlParameter(){ ParameterName="@USERID", Value = userid }
            };
            
            ExecuteNonQuery("APPUSER_CREATE", parameters);
        }

        public static void MemberCreate(string firstname, string lastname, DateTime datejoined, string userid)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@FIRSTNAME", Value = firstname },
                new SqlParameter() { ParameterName = "@LASTNAME", Value = lastname },
                new SqlParameter() { ParameterName = "@DATEJOINED", Value = datejoined },
                new SqlParameter() { ParameterName = "@ASPNETDB_USERID", Value = userid}
            };

            ExecuteNonQuery("MEMBER_CREATE", parameters);
        }

        public static void MemberDelete(string id)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ID", Value = id } };

            ExecuteNonQuery("MEMBER_DELETE", parameters);
        }

        public static void MemberEdit(string id, string firstname, string lastname, DateTime datejoined, string userid)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ID", Value = id },
                new SqlParameter() { ParameterName = "@FIRSTNAME", Value = firstname },
                new SqlParameter() { ParameterName = "@LASTNAME", Value = lastname },
                new SqlParameter() { ParameterName = "@DATEJOINED", Value = datejoined },
                new SqlParameter() { ParameterName = "@ASPNETDB_USERID", Value = userid}
            };

            ExecuteNonQuery("MEMBER_EDIT", parameters);
        }

        public static DataTable MemberSelect()
        {
            return RetrieveDataTable("MEMBER_SELECT", null);
        }

        public static DataTable MemberSelectTop(int rows)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ROWS", Value = rows } };

            return RetrieveDataTable("MEMBER_SELECTTOP", parameters);
        }

        public static void PreSaleCreate(string firstname, string lastname, string email, string birthdate, string streetaddress, string city, string state, string zipcode, string emergencycontact, string emergencycontactphone, string phone)
        {
            var parameters = new[]{
                new SqlParameter(){ ParameterName = "@FIRSTNAME", Value = firstname },
                new SqlParameter(){ ParameterName = "@LASTNAME", Value = lastname },
                new SqlParameter(){ ParameterName = "@EMAIL", Value = email },
                new SqlParameter(){ ParameterName = "@BIRTHDATE", Value = birthdate },
                new SqlParameter(){ ParameterName = "@STREETADDRESS", Value = streetaddress },
                new SqlParameter(){ ParameterName = "@CITY", Value = city },
                new SqlParameter(){ ParameterName = "@STATE", Value = state },
                new SqlParameter(){ ParameterName = "@ZIPCODE", Value = zipcode },
                new SqlParameter(){ ParameterName = "@EMERGENCYCONTACT", Value = emergencycontact },
                new SqlParameter(){ ParameterName = "@EMERGENCYCONTACTPHONE", Value = emergencycontactphone },
                new SqlParameter(){ ParameterName = "@PHONE", Value = phone }
            };

            ExecuteNonQuery("PRESALE_CREATE", parameters);
        }

        public static DataTable PreSaleSelect()
        {
            return RetrieveDataTable("PRESALE_SELECT", null);
        }

        public static DataTable PreSaleSelectTop(int rows)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ROWS", Value = rows } };

            return RetrieveDataTable("PRESALE_SELECTTOP", parameters);
        }

        public static void GearChange(bool status, string name)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@STATUS", Value = status },
                new SqlParameter() { ParameterName = "@NAME", Value = "MEMBERS" }
            };

            ExecuteNonQuery("GEARS_CHANGE", parameters);
        }

        public static bool GearSelect(string name)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@NAME", Value = "MEMBERS" }
            };

            return Convert.ToBoolean(RetrieveScalar("GEARS_SELECTTYPE", parameters));
        }

        public static ImageObj ImageCreate(string filename)
        {
            string uniquefilename = Cosmos.Common.UniqueFileName.Get(filename);

            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ID", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.UniqueIdentifier, Size = Guid.Empty.ToString().Length },
                new SqlParameter() { ParameterName = "@FILENAME", Value = uniquefilename }
            };

            ExecuteNonQuery("IMAGE_CREATE", parameters);
            
            return new ImageObj { ID = Guid.Parse(parameters[0].Value.ToString()), FilePath = uniquefilename };
        }

        //public static ImageObj ImageSelect(string id)
        //{
        //    var parameters = new[]{
        //        new SqlParameter() { ParameterName = "@ID", Value = id }
        //    };
            
        //    Image image;

        //    DataTable dt = RetrieveDataTable("IMAGE_SELECT", parameters);

        //    string ext = dt.Rows[0]["EXT"].ToString();
        //    byte[] imageArray = (byte[])dt.Rows[0]["IMAGEDATA"];

        //    using(MemoryStream stream = new MemoryStream(imageArray))
        //    {
        //        image = Image.FromStream(stream);                
        //    }

        //    return new ImageObj { Extension = ext, ImageData = image };
        //}

        public static Guid GalleryCreate(string title, string userid)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ID", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.UniqueIdentifier, Size = Guid.Empty.ToString().Length },
                new SqlParameter() { ParameterName = "@TITLE", Value = title },
                new SqlParameter() { ParameterName = "@ASPNETDB_USERID", Value = userid}
            };

            ExecuteNonQuery("GALLERY_CREATE", parameters);

            return Guid.Parse(parameters[0].Value.ToString());
        }

        public static void GalleryEdit(string id, string title, string userid)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ID", Value = id },
                new SqlParameter() { ParameterName = "@TITLE", Value = title },
                new SqlParameter() { ParameterName = "@ASPNETDB_USERID", Value = userid}
            };

            ExecuteNonQuery("GALLERY_EDIT", parameters);
        }

        public static DataTable GallerySelect()
        {
            return RetrieveDataTable("GALLERY_SELECT", null);
        }

        public static string GallerySelectName(string id)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ID", Value = id }
            };

            return RetrieveScalar("GALLERY_SELECTNAME", parameters).ToString();
        }

        public static void GalleryImageCreate(string galleryid, string imageid, string userid, string title, string description)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@GALLERYID", Value = galleryid },
                new SqlParameter() { ParameterName = "@IMAGEID", Value = imageid },
                new SqlParameter() { ParameterName = "@ASPNETDB_USERID", Value = userid},
                new SqlParameter() { ParameterName = "@TITLE", Value = title},
                new SqlParameter() { ParameterName = "@DESCRIPTION", Value = description}
            };

            ExecuteNonQuery("GALLERYIMAGE_CREATE", parameters);     
        }

        public static List<GalleryImage> GalleryImageSelect(string galleryid)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@GALLERYID", Value = galleryid }
            };
            
            DataTable dt = RetrieveDataTable("GALLERYIMAGE_SELECTFROMGALLERY", parameters);

            List<GalleryImage> imgs = new List<GalleryImage>();

            foreach (DataRow row in dt.Rows)
            {
                imgs.Add(new GalleryImage{ Title = row["TITLE"].ToString(),
                                            Description = row["DESCRIPTION"].ToString(), 
                                            Filename = row["FILENAME"].ToString(),
                                            ImageID = Guid.Parse(row["IMAGEID"].ToString()) });
            }

            return imgs;
        }

        public static void GalleryImageUpdate(string galleryid, string imageid, string userid, string title, string description)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@GALLERYID", Value = galleryid },
                new SqlParameter() { ParameterName = "@IMAGEID", Value = imageid },
                new SqlParameter() { ParameterName = "@ASPNETDB_USERID", Value = userid},
                new SqlParameter() { ParameterName = "@TITLE", Value = title},
                new SqlParameter() { ParameterName = "@DESCRIPTION", Value = description}
            };

            ExecuteNonQuery("GALLERYIMAGE_UPDATE", parameters);
        }

        public static string GalleryImageDelete(string galleryid, string imageid)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@GALLERYID", Value = galleryid },
                new SqlParameter() { ParameterName = "@IMAGEID", Value = imageid },
                new SqlParameter() { ParameterName = "@FILENAME", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.NVarChar, Size = 1000 }
            };

            ExecuteNonQuery("GALLERYIMAGE_DELETE", parameters);

            return parameters[2].Value.ToString();
        }

        public static List<GalleryImage> GalleryImageSelectSlideshow()
        {
            string galleryid = RetrieveScalar("GALLERYIMAGE_SELECTSLIDESHOWID", null).ToString();

            var parameters = new[]{
                new SqlParameter() { ParameterName = "@GALLERYID", Value = galleryid }
            };

            DataTable dt = RetrieveDataTable("GALLERYIMAGE_SELECTFROMGALLERY", parameters);

            List<GalleryImage> imgs = new List<GalleryImage>();

            foreach (DataRow row in dt.Rows)
            {
                imgs.Add(new GalleryImage
                {
                    Title = row["TITLE"].ToString(),
                    Description = row["DESCRIPTION"].ToString(),
                    Filename = row["FILENAME"].ToString(),
                    ImageID = Guid.Parse(row["IMAGEID"].ToString())
                });
            }

            return imgs;
        }

        public static DataTable GalleryImageSelect()
        {
            return RetrieveDataTable("GALLERYIMAGE_SELECT", null);
        }

        public static DataTable DescriptionGet(string pagename)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@PAGENAME", Value = pagename }
            };

            return RetrieveDataTable("META_SELECTDESCRIPTION", parameters);
        }

        public static DataTable KeywordsGet(string pagename)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@PAGENAME", Value = pagename }
            };

            return RetrieveDataTable("META_SELECTKEYWORDS", parameters);
        }

        public static string TitleGet(string pagename)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@PAGENAME", Value = pagename }
            };

            return RetrieveScalar("META_SELECTTITLE", parameters).ToString();
        }

        public static DataTable MetaGet(string pagename)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@PAGENAME", Value = pagename }
            };

            return RetrieveDataTable("META_SELECTFROMPAGENAME", parameters);
        }

        public static DataTable PagesGet()
        {
            return RetrieveDataTable("PAGENAME_SELECT", null);
        }

        public static DataTable MetaTypesSelect()
        {
            return RetrieveDataTable("METATYPE_SELECT", null);
        }

        public static void MetaCreate(string description, Guid pagenameid, Guid metatypeid)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@DESCRIPTION", Value = description },
                new SqlParameter() { ParameterName = "@METATYPEID", Value = metatypeid },
                new SqlParameter() { ParameterName = "@PAGENAMEID", Value = pagenameid }
            };

            ExecuteNonQuery("META_CREATE", parameters);
        }

        public static DataTable MetaSelect()
        {
            return RetrieveDataTable("META_SELECT", null);
        }

        public static DataTable WallsGet()
        {
            return RetrieveDataTable("WALLS_SELECT", null);
        }

        public static string WallSelectName(string id)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ID", Value = id }
            };

            return RetrieveScalar("WALL_SELECTNAME", parameters).ToString();
        }

        public static DataTable RoutesSelect(string wallid)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ID", Value = wallid }
            };

            return RetrieveDataTable("ROUTES_SELECT", parameters);
        }

        public static DataTable RoutesSelectAll()
        {
            return RetrieveDataTable("ROUTES_SELECTALL", null);
        }

        public static void RoutesCreate(string name, string description, string setterid, string ratingstatusid, string wallid, string userid, string dateset, string dateexp)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@NAME", Value = name },
                new SqlParameter() { ParameterName = "@DESCRIPTION", Value = description },
                new SqlParameter() { ParameterName = "@ASPNETDB_USERID", Value = userid },
                new SqlParameter() { ParameterName = "@SETTERID", Value = setterid },
                new SqlParameter() { ParameterName = "@RATINGSTATUSID", Value = ratingstatusid },
                new SqlParameter() { ParameterName = "@WALLID", Value = wallid },
                new SqlParameter() { ParameterName = "@DATESET", Value = dateset },
                new SqlParameter() { ParameterName = "@DATEEXP", Value = dateexp }
            };

            ExecuteNonQuery("ROUTES_CREATE", parameters); 
        }

        public static void RoutesEdit(string id, string name, string description, string setterid, string ratingstatusid, string wallid, string userid, DateTime dateset, DateTime dateexp)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ID", Value = id },
                new SqlParameter() { ParameterName = "@NAME", Value = name },
                new SqlParameter() { ParameterName = "@DESCRIPTION", Value = description },
                new SqlParameter() { ParameterName = "@ASPNETDB_USERID", Value = userid },
                new SqlParameter() { ParameterName = "@SETTERID", Value = setterid },
                new SqlParameter() { ParameterName = "@RATINGSTATUSID", Value = ratingstatusid },
                new SqlParameter() { ParameterName = "@WALLID", Value = wallid },
                new SqlParameter() { ParameterName = "@DATESET", Value = dateset },
                new SqlParameter() { ParameterName = "@DATEEXP", Value = dateexp }
            };

            ExecuteNonQuery("ROUTES_EDIT", parameters);
        }

        public static DataTable RatingsSelectAll()
        {
            return RetrieveDataTable("RATINGS_SELECTALL", null);
        }

        public static DataTable SettersSelectAll()
        {
            return RetrieveDataTable("SETTERS_SELECTALL", null);
        }

        public static bool IsVideo(string galleryid)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@GALLERYID", Value = galleryid }
            };
            
            object o = RetrieveScalar("GALLERY_ISVIDEO", parameters);

            return (bool)o;
        }

        public static void RoutesDelete(string routeid)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ROUTEID", Value = routeid } };

            ExecuteNonQuery("ROUTE_DELETE", parameters);
        }

        public static DataTable RoutesSelectOne(string routeid)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ROUTEID", Value = routeid } };

            return RetrieveDataTable("ROUTE_SELECT", parameters);
        }

        public static DataTable VideoSelect()
        {
            return RetrieveDataTable("VIDEO_SELECT", null);
        }

        public static void VideoCreate(string title, string link, string description, string userid)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@TITLE", Value = title },
                new SqlParameter() { ParameterName = "@DESCRIPTION", Value = description },
                new SqlParameter() { ParameterName = "@ASPNETDB_USERID", Value = userid },
                new SqlParameter() { ParameterName = "@LINK", Value = link }
            };

            ExecuteNonQuery("VIDEO_CREATE", parameters);        
        }

        public static void VideoDelete(string id)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ID", Value = id }
            };

            ExecuteNonQuery("VIDEO_DELETE", parameters);
        }

        public static void VideoUpdate(string id, string title, string description, string userid, string link)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ID", Value = id },
                new SqlParameter() { ParameterName = "@TITLE", Value = title },
                new SqlParameter() { ParameterName = "@DESCRIPTION", Value = description },
                new SqlParameter() { ParameterName = "@ASPNETDB_USERID", Value = userid},
                new SqlParameter() { ParameterName = "@LINK", Value = link}
            };

            ExecuteNonQuery("VIDEO_EDIT", parameters);
        }

        public static DataTable RoutesSelectTopFour()
        {
            return RetrieveDataTable("ROUTES_SELECTTOPFOUR", null);
        }

        public static string GetWallImage(string id)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ID", Value = id }
            };

            return RetrieveScalar("WALLIMAGE_SELECT", parameters).ToString();
        }

        public static void SetterDelete(string id)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ID", Value = id }
            };

            ExecuteNonQuery("SETTER_DELETE", parameters);
        }

        public static DataTable SetterSelect()
        {
            return RetrieveDataTable("SETTER_SELECT", null);
        }

        public static void SetterUpdate(string id, string name)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@ID", Value = id },
                new SqlParameter() { ParameterName = "@NAME", Value = name }
            };

            ExecuteNonQuery("SETTER_UPDATE", parameters);
        }

        public static void SetterCreate(string name)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@NAME", Value = name }
            };

            ExecuteNonQuery("SETTER_CREATE", parameters);
        }

        public static DataTable GetChartData(ClimbingFloor climbingFloor)
        {
            var parms = new [] {
                new SqlParameter() { ParameterName = "@FLOOR", Value = climbingFloor }
            };

            return RetrieveDataTable("RATING_GETCHARTSTATS_ALL", parms);
        }

        public static DataTable GetChartData(string wallid)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@WALLID", Value = wallid }
            };

            return RetrieveDataTable("RATING_GETCHARTSTATS", parameters);
        }

        public static DataTable GetChartData()
        {            
            return RetrieveDataTable("RATING_GETCHARTSTATS_ALL", null);
        }


        public static DataTable GetStyles()
        {
            return RetrieveDataTable("STYLETEMPLATE_GET", null);
        }

        public static LatestBlurb GetLatestBlurb()
        {
            LatestBlurb blurb = null;
            DataTable dt = RetrieveDataTable("LATESTBLURB_GET", null);

            if(dt != null && dt.Rows.Count > 0)
            {
                blurb = new LatestBlurb();
                
                foreach (DataRow row in dt.Rows)
                {
                    blurb.Message = row["BLURB"].ToString();
                    blurb.Title = row["TITLE"].ToString();
                    blurb.MessageStyleID = new Guid(row["BLURBSTYLETEMPLATEID"].ToString());
                    blurb.TitleStyleID = new Guid(row["TITLESTYLETEMPLATEID"].ToString());
                    blurb.MessageStyle = row["BLURBSTYLE"].ToString();
                    blurb.TitleStyle = row["TITLESTYLE"].ToString();
                }
            }

            return blurb;
        }

        public static void UpdateLatestBlurb(LatestBlurb blurb)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@BLURB", Value = blurb.Message },
                new SqlParameter() { ParameterName = "@TITLE", Value = blurb.Title },
                new SqlParameter() { ParameterName = "@BLURBSTYLETEMPLATEID", Value = blurb.MessageStyleID },
                new SqlParameter() { ParameterName = "@TITLESTYLETEMPLATEID", Value = blurb.TitleStyleID }
            };

            ExecuteNonQuery("LATESTBLURB_UPDATE", parameters);
        }

        public static void RoutesDeleteAll()
        {
            ExecuteNonQuery("ROUTES_DELETEALL", null);
        }

        public static string GetHours()
        {
            var temp = RetrieveScalar("HOURS_GET", null);

            if(temp != null)
                return temp.ToString();
            else
                return string.Empty;
        }

        public static void UpdateHours(string hours, string userid)
        {
            var parameters = new[]{
                new SqlParameter() { ParameterName = "@HOURSTEXT", Value = hours },
                new SqlParameter() { ParameterName = "@ASPNETDB_USERID", Value = userid }
            };

            ExecuteNonQuery("HOURS_UPDATE", parameters);
        }
    }
}